package ge.gen.eBdgt.hibernate;

import ge.gen.eBdgt.vo.CmuTrackVO;
import ge.gen.eBdgt.vo.IcnVO;
import ge.gen.eBdgt.vo.TeamWrkLoadVO;

import java.util.List;

import org.hibernate.Criteria;
import org.hibernate.HibernateException;
import org.hibernate.Session;
import org.hibernate.Transaction;
import org.hibernate.criterion.Disjunction;
import org.hibernate.criterion.Expression;
import org.hibernate.criterion.Restrictions;

public class HibernateUtils {

	private IcnVO getInsertedItems(IcnVO vo, IcnVO voToBeUpdated) {
		voToBeUpdated.setOrderDate(vo.getOrderDate());
		voToBeUpdated.setDescription(vo.getDescription());
		voToBeUpdated.setOutageDate(vo.getOutageDate());
		voToBeUpdated.setRequestDate(vo.getRequestDate());
		voToBeUpdated.setStatus(vo.getStatus());
		voToBeUpdated.setCustomer(vo.getCustomer());
		voToBeUpdated.setDes(vo.getDes());
		voToBeUpdated.setEe(vo.getEe());
		voToBeUpdated.setGenSn(vo.getGenSn());
		voToBeUpdated.setIcn(vo.getIcn());
		voToBeUpdated.setIcn2nd(vo.getIcn2nd());
		voToBeUpdated.setMe(vo.getMe());
		voToBeUpdated.setModDwg(vo.getModDwg());
		voToBeUpdated.setPm(vo.getPm());
		voToBeUpdated.setReqEng(vo.getReqEng());
		voToBeUpdated.setStation(vo.getStation());
		voToBeUpdated.setTimesNo(vo.getTimesNo());
		return voToBeUpdated;
	}

	private CmuTrackVO getInsertedItems(CmuTrackVO vo, CmuTrackVO voToBeUpdated) {
		voToBeUpdated.setCtgryTyp(vo.getCtgryTyp());
		voToBeUpdated.setEndDate(vo.getEndDate());
		voToBeUpdated.setMilstnDate(vo.getMilstnDate());
		voToBeUpdated.setPlanHrs(vo.getPlanHrs());
		return voToBeUpdated;
	}

	private IcnVO getUpdatedItems(IcnVO vo, IcnVO voToBeUpdated) {
		if (vo.isModified(IcnVO.ICN)) {
			voToBeUpdated.setIcn(vo.getIcn());
		}
		if (vo.isModified(IcnVO.ICN2ND)) {
			voToBeUpdated.setIcn2nd(vo.getIcn2nd());
		}
		if (vo.isModified(IcnVO.CUSTOMER)) {
			voToBeUpdated.setCustomer(vo.getCustomer());
		}
		if (vo.isModified(IcnVO.DES)) {
			voToBeUpdated.setDes(vo.getDes());
		}
		if (vo.isModified(IcnVO.DESCRIPTION)) {
			voToBeUpdated.setDescription(vo.getDescription());
		}
		if (vo.isModified(IcnVO.EE)) {
			voToBeUpdated.setEe(vo.getEe());
		}
		if (vo.isModified(IcnVO.GENSN)) {
			voToBeUpdated.setGenSn(vo.getGenSn());
		}
		if (vo.isModified(IcnVO.ME)) {
			voToBeUpdated.setMe(vo.getMe());
		}
		if (vo.isModified(IcnVO.MODDWG)) {
			voToBeUpdated.setModDwg(vo.getModDwg());
		}
		if (vo.isModified(IcnVO.ORDERDATE)) {
			voToBeUpdated.setOrderDate(vo.getOrderDate());
		}
		if (vo.isModified(IcnVO.OUTAGEDATE)) {
			voToBeUpdated.setOutageDate(vo.getOutageDate());
		}
		if (vo.isModified(IcnVO.PM)) {
			voToBeUpdated.setPm(vo.getPm());
		}
		if (vo.isModified(IcnVO.REQENG)) {
			voToBeUpdated.setReqEng(vo.getReqEng());
		}
		if (vo.isModified(IcnVO.REQUESTDATE)) {
			voToBeUpdated.setRequestDate(vo.getRequestDate());
		}
		if (vo.isModified(IcnVO.STATION)) {
			voToBeUpdated.setStation(vo.getStation());
		}
		if (vo.isModified(IcnVO.STATUS)) {
			voToBeUpdated.setStatus(vo.getStatus());
		}
		if (vo.isModified(IcnVO.TIMESNO)) {
			voToBeUpdated.setTimesNo(vo.getTimesNo());
		}
		return voToBeUpdated;
	}

	public boolean updateIcnVO(IcnVO vo) {
		Session session = SessionFactoryHelper.getSessionFactory()
				.openSession();
		try {
			Transaction tx = session.beginTransaction();
			session.update(vo);

			tx.commit();
		} catch (HibernateException e) {
			e.printStackTrace();
			return false;
		} finally {
			if (session != null) {
				session.clear();
			}
		}
		return true;
	}

	public boolean updateCmuTrack(CmuTrackVO vo) {
		Session session = SessionFactoryHelper.getSessionFactory()
				.openSession();
		try {
			Transaction tx = session.beginTransaction();
			session.update(vo);
			tx.commit();
		} catch (HibernateException e) {
			e.printStackTrace();
			return false;
		} finally {
			if (session != null) {
				session.clear();
			}
		}
		return true;

	}

	public boolean saveIcn(IcnVO vo) {
		Session session = SessionFactoryHelper.getSessionFactory()
				.openSession();
		try {
			Transaction tx = session.beginTransaction();
			session.save(vo);
			tx.commit();
		} catch (HibernateException e) {
			e.printStackTrace();
			return false;
		} finally {
			if (session != null) {
				session.clear();
			}
		}
		return true;
	}

	public boolean saveCmuTrack(CmuTrackVO vo) {
		Session session = SessionFactoryHelper.getSessionFactory()
				.openSession();
		try {
			Transaction tx = session.beginTransaction();
			session.save(vo);
			tx.commit();
		} catch (HibernateException e) {
			e.printStackTrace();
			return false;
		} finally {
			if (session != null) {
				session.clear();
			}
		}
		return true;
	}

	public boolean deleteIcn(String icn) {
		Session session = SessionFactoryHelper.getSessionFactory()
				.openSession();
		try {
			IcnVO icnVO = getIcnVO(icn);
			session.beginTransaction();
			session.delete(icnVO);
			session.getTransaction().commit();
		} catch (HibernateException e) {
			e.printStackTrace();
			return false;
		} finally {
			if (session != null) {
				session.clear();
			}
		}
		return true;
	}

	public List getIcnList() {
		Session session = SessionFactoryHelper.getSessionFactory()
				.openSession();
		List icnList = null;
		try {
			session.beginTransaction();
			icnList = session.createSQLQuery("SELECT * from GEN_WORK_LOAD")
					.addEntity(IcnVO.class).list();
			session.getTransaction().commit();
		} catch (Exception e) {
			session.getTransaction().rollback();
			e.printStackTrace();
		} finally {
			if (session != null) {
				session.clear();
			}
		}
		return icnList;
	}

	public IcnVO getIcnVO(String icn) {
		Session session = SessionFactoryHelper.getSessionFactory()
				.openSession();
		IcnVO voToBeRead = new IcnVO();
		try {
			session.beginTransaction();
			voToBeRead = (IcnVO) session.get(IcnVO.class, icn);
			CmuTrackVO ordr = voToBeRead.getOrderInfo();
			ordr.getEndDate();
		} catch (Exception e) {
			session.getTransaction().rollback();
			e.printStackTrace();
		} finally {
			if (session.isOpen()) {
				session.flush();
				session.close();
			}
		}
		return voToBeRead;
	}

	public CmuTrackVO getCmuTrackVO(String timesNo) {
		Session session = SessionFactoryHelper.getSessionFactory()
				.openSession();
		CmuTrackVO voToBeRead = new CmuTrackVO();
		try {
			session.beginTransaction();
			voToBeRead = (CmuTrackVO) session.get(CmuTrackVO.class, timesNo);
		} catch (Exception e) {
			session.getTransaction().rollback();
			e.printStackTrace();
		} finally {
			if (session.isOpen()) {
				session.flush();
				session.close();
			}
		}
		return voToBeRead;
	}

	public static List searchByCustomer(String customer) {
		Session session = SessionFactoryHelper.getSessionFactory()
				.openSession();
		Criteria crit = session.createCriteria(IcnVO.class);
		Disjunction or = Restrictions.disjunction();
		if (customer != null) {
			or.add(Restrictions.like("customer", "%" + customer + "%"));
			crit.add(or);
		}
		return crit.list();
	}

	public List<IcnVO> searchByAddlScope(String searchValue) {
		Session session = SessionFactoryHelper.getSessionFactory()
				.openSession();
		Criteria crit = session.createCriteria(IcnVO.class);
		Disjunction or = Restrictions.disjunction();
		if (searchValue != null) {
			or.add(Restrictions.like("addlScope1", "%" + searchValue + "%"));
			or.add(Restrictions.like("addlScope2", "%" + searchValue + "%"));
			or.add(Restrictions.like("addlScope3", "%" + searchValue + "%"));
			or.add(Restrictions.like("addlScope4", "%" + searchValue + "%"));
			crit.add(or);
		}
		return crit.list();
	}

	public List<IcnVO> searchByMainScope(String searchValue) {
		Session session = SessionFactoryHelper.getSessionFactory()
				.openSession();
		Criteria crit = session.createCriteria(IcnVO.class);
		Disjunction or = Restrictions.disjunction();
		if (searchValue != null) {
			or.add(Restrictions.like("mainWorkScope1", "%" + searchValue + "%")
					.ignoreCase());
			or.add(Restrictions.like("mainWorkScope2", "%" + searchValue + "%")
					.ignoreCase());
			crit.add(or);
		}
		return crit.list();
	}

	public List<IcnVO> searchByReqEngr(String searchValue) {
		Session session = SessionFactoryHelper.getSessionFactory()
				.openSession();
		Criteria crit = session.createCriteria(IcnVO.class);
		Disjunction or = Restrictions.disjunction();
		if (searchValue != null) {
			or.add(Restrictions.like("reqEng", "%" + searchValue + "%"));
			crit.add(or);
		}
		return crit.list();
	}

	public List<IcnVO> searchBySerNum(String searchValue) {
		Session session = SessionFactoryHelper.getSessionFactory()
				.openSession();
		Criteria crit = session.createCriteria(IcnVO.class);
		Disjunction or = Restrictions.disjunction();
		if (searchValue != null) {
			or.add(Restrictions.like("customer", "%" + searchValue + "%")
					.ignoreCase());
			crit.add(or);
		}
		return crit.list();
	}

	public List<IcnVO> searchByStation(String searchValue) {
		Session session = SessionFactoryHelper.getSessionFactory()
				.openSession();
		Criteria crit = session.createCriteria(IcnVO.class);
		Disjunction or = Restrictions.disjunction();
		if (searchValue != null) {
			or.add(Restrictions.like("station", "%" + searchValue + "%"));
			crit.add(or);
		}
		return crit.list();
	}

	public static List getDescriptionCriteria(String description) {
		Session session = SessionFactoryHelper.getSessionFactory()
				.openSession();
		Criteria crit = session.createCriteria(IcnVO.class);
		Disjunction or = Restrictions.disjunction();
		if (description != null) {
			or.add(Restrictions.like("description", "%" + description + "%"));
			or.add(Restrictions.like("addlScope1", "%" + description + "%"));
			or.add(Restrictions.like("addlScope2", "%" + description + "%"));
			or.add(Restrictions.like("addlScope3", "%" + description + "%"));
			or.add(Restrictions.like("addlScope4", "%" + description + "%"));
			or.add(Restrictions.like("notes", "%" + description + "%"));
			crit.add(or);
		}
		return crit.list();
	}

	public static List searchByIcn(String icn) {
		Session session = SessionFactoryHelper.getSessionFactory()
				.openSession();
		Criteria crit = session.createCriteria(IcnVO.class);
		Disjunction or = Restrictions.disjunction();
		if (icn != null) {
			or.add(Restrictions.like("icn", "%" + icn + "%"));
			or.add(Restrictions.like("icn2nd", "%" + icn + "%"));
			crit.add(or);
		}
		return crit.list();
	}

	public static List getStatusCriteria(String status) {
		Session session = SessionFactoryHelper.getSessionFactory()
				.openSession();
		Criteria criteria = session.createCriteria(IcnVO.class);
		if (status != null) {
			criteria.add(Expression.eq("status", status));
		}
		return criteria.list();
	}

	public List getCmuTrackList() {
		Session session = SessionFactoryHelper.getSessionFactory()
				.openSession();
		List cmuTrackList = null;
		try {
			session.beginTransaction();
			cmuTrackList = session.createSQLQuery("SELECT * from CMU_TRACK")
					.addEntity(CmuTrackVO.class).list();
			session.getTransaction().commit();
		} catch (Exception e) {
			session.getTransaction().rollback();
			e.printStackTrace();
		} finally {
			if (session.isOpen()) {
				session.flush();
				session.close();
			}
		}
		return cmuTrackList;
	}

	public List<TeamWrkLoadVO> getTeamWrkLoad() {
		Session session = SessionFactoryHelper.getSessionFactory()
				.openSession();
		List cmuTrackList = null;
		try {
			session.beginTransaction();
			cmuTrackList = session.createSQLQuery("SELECT ICN, REQ_ENG from GEN_WORK_LOAD GROUP BY  REQ_ENG, ICN")
					.addEntity(TeamWrkLoadVO.class).list();
			session.getTransaction().commit();
		} catch (Exception e) {
			session.getTransaction().rollback();
			e.printStackTrace();
		} finally {
			if (session.isOpen()) {
				session.flush();
				session.close();
			}
		}
		return cmuTrackList;
	}

}
